package link.wangkui;

import cn.hutool.poi.excel.ExcelUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import link.wangkui.utils.TestFileUtil;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.junit.Test;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class EasyExcelUtil {
    private List<DemoData> data() {
    return data(10);
    }
    private List<DemoData> data(int n) {
        List<DemoData> list = new ArrayList<>();
        for (int i = 0; i < n; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + i);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }
    /**
     * 最简单的写
     * <p>1. 创建excel对应的实体对象 参照{@link com.alibaba.easyexcel.test.demo.write.DemoData}
     * <p>2. 直接写即可
     */
    @Test
    public void simpleWrite() {
        String fileName = TestFileUtil.getPath() + "write" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去读，然后写到第一个sheet，名字为模板 然后文件流会自动关闭

        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
    }
    /**
     * 使用table去写入
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link DemoData}
     * <p>
     * 2. 然后写入table即可
     */
    @Test
    public void tableWrite() {
        String fileName = TestFileUtil.getPath() + "tableWrite" + System.currentTimeMillis() + ".xlsx";
        System.out.printf(fileName);
        // 标题样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为红色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short)12);
        headWriteFont.setFontName("宋体");
        headWriteCellStyle.setWriteFont(headWriteFont);
        //表格的内容样式
        WriteCellStyle bodyStyle = new WriteCellStyle();
        // 字体样式
        bodyStyle.setWriteFont(TestFileUtil.setFont(12, "Arial", false));
        // 设置边框
        bodyStyle.setBorderLeft(BorderStyle.THIN);
        bodyStyle.setBorderRight(BorderStyle.THIN);
        bodyStyle.setBorderBottom(BorderStyle.THIN);

// 1>创建最后一行的样式
        WriteCellStyle lastRow = new WriteCellStyle();
// 2>设置加粗，其他样式保持一致
        lastRow.setWriteFont(TestFileUtil.setFont(14, "Arial", true));
        lastRow.setBorderLeft(BorderStyle.THIN);
        lastRow.setBorderRight(BorderStyle.THIN);
        lastRow.setBorderBottom(BorderStyle.THIN);
// 3>根据总行数]，叠加生成最后一行的样式List
        List<WriteCellStyle> preList = new ArrayList<>();
// 4>data是生成的表格数据集合，data.size()-1表示除最后一行的行数量
        for (int i = 0; i < data().size()- 1; i++) {
                preList.add(bodyStyle);
        }
// 5>添加最后一行的样式
        preList.add(lastRow);
// 6>创建策略，headStyle是头部样式，这里我就不写出来了
        HorizontalCellStyleStrategy dataTableStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, preList);
        // 方法1 这里直接写多个table的案例了，如果只有一个 也可以直一行代码搞定，参照其他案
        // 这里 需要指定写用哪个class去写
        ExcelWriter excelWriter = null;
        try  {

            excelWriter = EasyExcel.write(fileName, DemoData.class).inMemory(true).build();
            // 把sheet设置为不需要头 不然会输出sheet的头 这样看起来第一个table 就有2个头了
            WriteSheet writeSheet = EasyExcel.writerSheet("模板").needHead(Boolean.FALSE).build();
            // 这里必须指定需要头，table 会继承sheet的配置，sheet配置了不需要，table 默认也是不需要
            WriteTable writeTable0 = EasyExcel.writerTable(0).registerWriteHandler(dataTableStrategy).needHead(Boolean.TRUE).build();
            // .registerWriteHandler(dataTableStrategy)
            excelWriter.write(data(), writeSheet, writeTable0);
            preList.set(2,lastRow);
            HorizontalCellStyleStrategy dataTableStrategy1 = new HorizontalCellStyleStrategy(headWriteCellStyle, preList);
            WriteTable writeTable1 = EasyExcel.writerTable(1).registerWriteHandler(dataTableStrategy1).needHead(Boolean.TRUE).build();
            // 第二次写如也会创建头，然后在第一次的后面写入数据
            excelWriter.write(data(4), writeSheet, writeTable1);
        }catch(Exception e){
        }finally {
            if (excelWriter!=null){
                excelWriter.finish();
            }
        }
    }
    @Test
    public void lastRowStyleWrite() {
        String fileName = TestFileUtil.getPath() + "lastRowStyle" + System.currentTimeMillis() + ".xlsx";
        List<DemoData> list = data();
        // 标题样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为红色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short)12);
        headWriteFont.setFontName("宋体");
        headWriteCellStyle.setWriteFont(headWriteFont);


        //表格的内容样式
        WriteCellStyle bodyStyle = new WriteCellStyle();
        // 字体样式
        bodyStyle.setWriteFont(TestFileUtil.setFont(12, "Arial", false));
        // 设置边框
        bodyStyle.setBorderLeft(BorderStyle.THIN);
        bodyStyle.setBorderRight(BorderStyle.THIN);
        bodyStyle.setBorderBottom(BorderStyle.THIN);

// 1>创建最后一行的样式
        WriteCellStyle lastRow = new WriteCellStyle();
// 2>设置加粗，其他样式保持一致
        lastRow.setWriteFont(TestFileUtil.setFont(14, "Arial", true));
        lastRow.setBorderLeft(BorderStyle.THIN);
        lastRow.setBorderRight(BorderStyle.THIN);
        lastRow.setBorderBottom(BorderStyle.THIN);
// 3>根据总行数]，叠加生成最后一行的样式List
        List<WriteCellStyle> preList = new ArrayList<>();
// 4>data是生成的表格数据集合，data.size()-1表示除最后一行的行数量
        for (int i = 0; i < list.size()- 1; i++) {
            preList.add(bodyStyle);
        }
// 5>添加最后一行的样式
        preList.add(lastRow);
// 6>创建策略，headStyle是头部样式，这里我就不写出来了
        HorizontalCellStyleStrategy dataTableStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, preList);
        EasyExcel.write(fileName, DemoData.class)
                .registerWriteHandler(dataTableStrategy)
                .sheet("模板")
                .doWrite(list);
    }
}
